package com.eascs.web.o2o.scheduler.service;

import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StopWatch;
import com.eascs.web.o2o.scheduler.entity.PubDatadictionary;
import com.eascs.web.o2o.scheduler.entity.PubDditem;
import javax.inject.Inject;

import org.springframework.stereotype.Service;

@Service
public class SynchroCrm {

	protected final Logger logger = LoggerFactory.getLogger(getClass());

	@Inject
	CrmService crmService;
	@Inject
	ServiceService serviceService;
	@Inject
	WorkflowService workflowService;

	/**
	 * SD数据每天全量
	 * 注释不要打开，打开记得还原！！！
	 * 
	 * 2016年7月2日
	 */
	public void saveData() {
		StopWatch stopWatch = new StopWatch();
		logger.info("*******************[CRM]同步数据开始*******************");
		saveEabaCustomer();
		logger.info("*******************[CRM]同步数据结束，共耗时：[{}]分*******************", stopWatch.elapsedTime()/60000);
	}
	
	
	/**
	 * 同步SD数据
	 */
	public void saveEabaCustomer() {
		StopWatch stopWatch = new StopWatch();
		
		logger.info("[CRM]同步SD数据开始......");
		String sql = "SELECT * from eadb.eaba_customer where  trunc(createtime) >= trunc(Sysdate-5)"
				+ " and trunc(createtime)<= trunc(Sysdate+2) ORDER BY cno";

		try {
			crmService.loadEabaCustomerData(sql);
			logger.info("[CRM]同步SD数据结束......");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("[CRM]Error：同步SD数据失败！！！");
		}
		
		logger.info("[CRM]同步SD结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
	}
	
	/**
	 * 数据字典
	 * 
	 * 2016年6月13日
	 */
	public void savePubDatadictionary() {
		StopWatch stopWatch = new StopWatch();
		logger.info("[CRM]同步PubDatadictionary开始......");
		
		try {
			crmService.loadData("SELECT * from eadb.pub_datadictionary order by ddno", PubDatadictionary.class,
					"delete from pub_datadictionary");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("[CRM]Error：同步PubDatadictionary失败！！！");
		}
		logger.info("[CRM]同步PubDatadictionary结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
	}
	
	/**
	 * 数据字典
	 * 
	 * 2016年6月13日
	 */
	public void savePubDditem() {
		StopWatch stopWatch = new StopWatch();
		logger.info("[CRM]同步PubDditem开始......");
		
		try {
			crmService.loadData("SELECT * from eadb.pub_dditem order by itemno", PubDditem.class, "delete from pub_dditem");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("[CRM]Error：同步PubDditem失败！！！");
		}
		logger.info("[CRM]同步PubDditem结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
	}
	
	/**
	 * 当有部分客户ID没有生成的时候，调用此方法，重新去更新code
	 * 
	 * 2016年7月20日
	 */
	public void updateCustomerCodeOnly() {
		//4.更新顾客信息客户code
		logger.info("[CRM]更新顾客信息客户code开始......");
		String sql="select customer_code, customer_id from customer_basic_info b where b.customer_code is null or b.customer_code=''";
		String expsql="update customer_basic_info set customer_code=? where customer_id=?" ;
		try {
			crmService.updateCustomerCode(sql, expsql,0);
			logger.info("[CRM]更新顾客信息客户code结束......");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("[CRM]Error：更新顾客信息客户code失败！！！");
		}
	}
	
	/**
	 * 根据指定客户ID去更新他的code
	 * 
	 * 2016年7月20日
	 */
	public void updateCustomerCodeById(String customerId) {
		//4.更新顾客信息客户code
		logger.info("[CRM]更新顾客[{}]客户code开始......", customerId);
		String expsql="update customer_basic_info set customer_code=? where customer_id=?" ;
		try {
			crmService.updateCustomerCodeById(expsql, customerId);
			logger.info("[CRM]更新顾客[{}]客户code结束......", customerId);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("[CRM]Error：更新顾客[{}]客户code失败！！！", customerId);
		}
	}
	
	
	
	
	

	
	/**
	 * @Title: synchcroCrm
	 * @Description: 同步到生产环境crm系统
	 * @author 张庆奇
	 * @return void
	 * @throws
	 * @date 2016年8月26日 下午2:52:38
	 */
	public void saveCustomerInfo() {
		//1. 删除掉 base_sequence表中以crm开头的数据
		logger.info("删除掉 base_sequence表中以crm开头的数据开始......");
		workflowService.excute("delete from base_sequence where seq_type like 'CRM%'");
		logger.info("删除掉 base_sequence表中以crm开头的数据结束......");
		//2.同步老系统的数据
		logger.info("同步老系统的crm数据开始......");
		String sql = "SELECT l.loanapplyno AS customer_id, l.loanapplyno AS customer_code, l.crmotoloanersur AS customer_family_name, l.crmotoloaner AS customer_given_name, l.loanername AS customer_name, l.cctpertrnothertel AS customer_name_pinyin, l.age, l.sex, l.idcardtype AS id_type, l.idcard AS id_number, l.mobile AS mobile_number, y.customer_type AS customer_type, '' AS is_new_customer, '' AS number_of_loans, y.customerno AS customer_sd_no, y.customercode AS customer_sd, y.customername AS customer_sd_name, CASE WHEN (l.MARRCHILDREN = 'UMM') THEN 1 WHEN (l.MARRCHILDREN = 'MAR6') THEN 3 ELSE 2 END AS marital_status, 0 AS customer_source, ( SELECT max(w.loanapplyno) FROM eadb.o2o_loaner w WHERE w.idcard = l.coupleidcard AND l.MARRCHILDREN IS NOT NULL ) AS mate_id,  y.createdate AS create_date,  y.createdate AS modify_date FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno WHERE l.loanapplyno IN ( SELECT max(l.loanapplyno) FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno WHERE y.islock = 'N'  AND l.MARRCHILDREN IS NOT NULL GROUP BY l.idcard ) ORDER BY l.loanapplyno";
		crmService.loadOldAddCrmData(sql);
		logger.info("同步老系统的crm数据结束......");
		
	}
	
	public void updateCustomerInfo(){
		String sql="select customer_id as loanapplyno,customer_id from customer_unit where unit_name ='O2O金融'";
	   String exeSql = "update customer_basic_info set customer_code=?  where customer_id=?";
	    crmService.updateCustomerCode(sql,exeSql,1);
	}
}
